﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using TpBaseObjects.Objects;
using TpContracts;
using TpExtensions;
using TpLogger;

namespace TpData.Layer {

    internal class TecNotizDataLayer : BaseSqlDataLayer {

        #region Members

        private const string getNotizUserSentMailsStatement = @"log.GetNotizUserSentMails";
        private const string loadNotizStatement = @"notiz.GetTelefonNotiz";
        private const string loadNotizListStatement = @"notiz.GetTelefonNotizList";
        private const string loadNotizTargetUserStatement = @"notiz.GetTelefonNotizTargetUser";
        private const string loadNotizStatesStatement = @"notiz.GetNotizTelStates";
        private const string loadNotizTelTypesStatement = @"notiz.GetNotizTelTypes";
        private const string saveNotizStatement = @"notiz.SaveTelefonNotiz";
        private const string saveNotizUserSentMailsStatement = @"log.SaveNotizUserSentMail";

        #endregion

        #region Load

        public virtual bool GetNotizUserSentMails(int userID, int notizID) {
            bool ret = false;
            string sql = getNotizUserSentMailsStatement;
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(sql, new SqlParameter("@UserID", userID), new SqlParameter("@NotizID", notizID))) {
                if (reader.Read()) {
                    ret = this.GetBoolean(reader, "Sent");
                }
            }
            return ret;
        }

        public virtual TecNotizObj LoadNotiz(int NotizID) {
            string sql = loadNotizStatement;
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(sql, new SqlParameter("@inNotizID", NotizID))) {
                return (reader.Read() ? LoadNotizFromDataReader(reader) : null);
            }
        }

        public virtual List<TecNotizObj> LoadNotizList() {
            return LoadNotizList(0);
        }

        public virtual List<TecNotizObj> LoadNotizList(int userID) {
            string sql = loadNotizListStatement;
            List<TecNotizObj> notizList = new List<TecNotizObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql, new SqlParameter("@UserID", userID), new SqlParameter("@logUserID", Contracts.MyUser.UserID))) {
                foreach (DataRow dr in dt.Rows) {
                    TecNotizObj no = new TecNotizObj();
                    no.NotizID = (int)dr["NotizID"];
                    no.ÄnderungDatum = (DateTime)dr["ÄnderungDatum"];
                    no.AnlageDatum = (DateTime)dr["AnlageDatum"];
                    no.AnrufType = (Extensions.TelType)dr["AnrufType"];
                    no.AnrufZeit = (DateTime)dr["AnrufZeit"];
                    no.Betreff = (string)dr["Betreff"];
                    no.NameAnrufer = (string)dr["NameAnrufer"];
                    no.NotizEMail = (string)dr["NotizEMail"];
                    no.NotizOrgKey = (int)dr["NotizOrgKey"];
                    no.NotizOrt = (string)dr["NotizOrt"];
                    no.NotizPlz = (string)dr["NotizPlz"];
                    no.NotizStrasse = (string)dr["NotizStrasse"];
                    no.Status = (byte)dr["Status"];
                    no.TecUserID = (int)dr["TecUserID"];
                    no.TelefonNummer = (string)dr["TelefonNummer"];
                    no.ZugeordneteUser = LoadNotizTargetUser(no.NotizID);
                    no.TecUser = (string)dr[17];
                    no.Sex = ((byte)dr["Sex"] == 1 ? Extensions.TpSex.Man.GetStringValue() : ((byte)dr["Sex"] == 2 ? Extensions.TpSex.Woman.GetStringValue() : Extensions.TpSex.Company.GetStringValue()));
                    notizList.Add(no);
                }
            }
            return notizList;
        }

        public virtual List<TecNotizStatus> LoadTelefonStates() {
            List<TecNotizStatus> listState = new List<TecNotizStatus>();
            string sql = loadNotizStatesStatement;
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql)) {
                foreach (DataRow dr in dt.Rows) {
                    TecNotizStatus st = new TecNotizStatus();
                    st.StatusID = (int)dr["StatusID"];
                    st.StatusText = (string)dr["StatusText"];
                    listState.Add(st);
                }

            }
            return listState;
        }

        public virtual List<TecNotizTelefonType> LoadTelefonTypes() {
            List<TecNotizTelefonType> listTypes = new List<TecNotizTelefonType>();
            string sql = loadNotizTelTypesStatement;
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql)) {
                foreach (DataRow dr in dt.Rows) {
                    TecNotizTelefonType st = new TecNotizTelefonType();
                    st.TelTypeID = (int)dr["AnrufTypeID"];
                    st.Typ = (string)dr["Typ"];
                    listTypes.Add(st);
                }

            }
            return listTypes;
        }

        public virtual List<TecNotizTargetUser> LoadNotizTargetUser() {
            return LoadNotizTargetUser(0);
        }

        public virtual List<TecNotizTargetUser> LoadNotizTargetUser(int notizID) {
            string sql = loadNotizTargetUserStatement;
            List<TecNotizTargetUser> tu = new List<TecNotizTargetUser>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql, new SqlParameter("@inNotizID", notizID))) {
                foreach (DataRow dr in dt.Rows) {
                    TecNotizTargetUser u = new TecNotizTargetUser();
                    u.LoginName = (string)dr["LoginName"];
                    u.Nachname = (string)dr["Nachname"];
                    u.NotizID = (int)dr["NotizID"];
                    u.TecUserID = (int)dr["UserID"];
                    u.Vorname = (string)dr["Vorname"];
                    u.EMail = (string)dr["EMail"];
                    tu.Add(u);
                }
            }
            return tu;
        }

        protected virtual TecNotizObj LoadNotizFromDataReader(IDataReader reader) {
            TecNotizObj n = new TecNotizObj();
            n.NotizID = this.GetInt32(reader, "NotizID");
            n.NameAnrufer = this.GetString(reader, "NameAnrufer");
            n.TelefonNummer = this.GetString(reader, "TelefonNummer");
            n.Betreff = this.GetString(reader, "Betreff");
            n.NotizStrasse = this.GetString(reader, "NotizStrasse");
            n.NotizPlz = this.GetString(reader, "NotizPlz");
            n.NotizOrt = this.GetString(reader, "NotizOrt");
            n.NotizEMail = this.GetString(reader, "NotizEMail");
            n.AnrufZeit = this.GetDateTime(reader, "AnrufZeit");
            n.AnrufType = (Extensions.TelType)this.GetInt32(reader, "AnrufType");
            n.TecUserID = this.GetInt32(reader, "TecUserID");
            n.AnlageDatum = this.GetDateTime(reader, "AnlageDatum");
            n.ÄnderungDatum = this.GetDateTime(reader, "ÄnderungDatum");
            n.ZugeordneteUser = LoadNotizTargetUser(n.NotizID);
            byte sex = this.GetByte(reader, "Sex");
            switch (sex) {
                case (byte)Extensions.TpSex.Man:
                    n.Sex = Extensions.TpSex.Man.GetStringValue();
                    break;
                case (byte)Extensions.TpSex.Woman:
                    n.Sex = Extensions.TpSex.Woman.GetStringValue();
                    break;
                case (byte)Extensions.TpSex.Company:
                    n.Sex = Extensions.TpSex.Company.GetStringValue();
                    break;
            }
            return n;
        }

        #endregion

        #region Save

        public virtual void SaveTecNotiz(TecNotizObj notiz) {
            SqlParameter[] parameters = GetParameters(notiz);
            int ret = ExecuteStoredProcedure(saveNotizStatement, parameters);
            if (ret == 0) {
                //log.Add("SaveTecNotiz: ExecuteStoredProcedure = 0", true);
            }
        }

        public virtual void SaveTecNotizUserSentMails(int userID, int notizID) {
            int ret = ExecuteStoredProcedure(saveNotizUserSentMailsStatement, new SqlParameter("@UserID", userID), new SqlParameter("@NotizID", notizID));
        }

        #endregion

        #region Functions

        protected virtual SqlParameter[] GetParameters(TecNotizObj notiz) {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("UserID");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            foreach (TecNotizTargetUser u in notiz.ZugeordneteUser) {
                if (u.InUse) {
                    DataRow dr = dt.NewRow();
                    dr["UserID"] = u.TecUserID;
                    dt.Rows.Add(dr);
                }
            }

            SqlParameter[] ps = new SqlParameter[15];

            ps[0] = new SqlParameter("@NotizID", notiz.NotizID);
            ps[1] = new SqlParameter("@Anrufzeit", notiz.AnrufZeit);
            ps[2] = new SqlParameter("@AnrufType", notiz.AnrufType);
            ps[3] = new SqlParameter("@TecUserID", notiz.TecUserID);
            ps[4] = new SqlParameter("@NotizOrgKey", notiz.NotizOrgKey);
            ps[5] = new SqlParameter("@NameAnrufer", notiz.NameAnrufer);
            ps[6] = new SqlParameter("@TelefonNummer", notiz.TelefonNummer);
            ps[7] = new SqlParameter("@Betreff", notiz.Betreff);
            ps[8] = new SqlParameter("@NotizStrasse", notiz.NotizStrasse);
            ps[9] = new SqlParameter("@NotizPlz", notiz.NotizPlz);
            ps[10] = new SqlParameter("@NotizOrt", notiz.NotizOrt);
            ps[11] = new SqlParameter("@NotizEMail", notiz.NotizEMail);
            ps[12] = new SqlParameter("@Users", dt);
            ps[13] = new SqlParameter("@Sex", (notiz.Sex == "Herr" ? (byte)Extensions.TpSex.Man : (notiz.Sex == "Frau" ? (byte)Extensions.TpSex.Woman : (byte)Extensions.TpSex.Company)));
            ps[14] = new SqlParameter("@Status", notiz.Status);
            return ps;
        }

        #endregion

    }
}
